#!/usr/bin/env python3  /*设定脚本文件直接能执行*/
# -*- coding:utf-8 -*-   /*设置编码*/
"""
@Author: zhuo
@Software: PyCharm
@File: tSQLiteHelper.py
@Time: 2025/2/28 14:39
@Function：ctSQLiteHelper:SQLite数据库操作模块
"""
# 导入SQLite模块
import sqlite3


class ctSQLiteHelper:
    def __init__(self, db_path):
        """
        function:  初始化数据库连接
              in:  db_path: 数据库文件路径
             out:  None
          return:  None
          others: Initialize SQLite connection
        """
        self.db_path = db_path
        self.conn = None
        self.cursor = None
        self.f_connectFunc()

    # <editor-fold desc="连接相关操作">
    # 建立数据库连接函数
    def f_connectFunc(self):
        """
        function:  建立数据库连接函数
              in:  None
             out:  None
          return:  None
          others:  Establish Database Connection Func
        """
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.cursor = self.conn.cursor()
        except sqlite3.Error as e:
            print(f"数据库连接失败: {e}")

    # 关闭数据库连接函数
    def f_closeFunc(self):
        """
        function:  关闭数据库连接函数
              in:  None
             out:  None
          return:  None
          others:  Close Database Connection Func
        """
        if self.conn:
            self.conn.close()
        self.conn = None
        self.cursor = None

    # </editor-fold>

    # <editor-fold desc="SQL执行操作">
    # 执行SQL语句（写操作）函数
    def f_executeFunc(self, sql, params=None):
        """
        function:  执行SQL语句（写操作）函数
              in:  sql: SQL语句；
                   params: 参数（可选）；
             out:  None
          return:  受影响的行数（失败返回-1）
          others:  Execute Write Operation SQL Func
        """
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            self.conn.commit()
            return self.cursor.rowcount
        except sqlite3.Error as e:
            self.conn.rollback()
            return -1

    # 执行查询语句函数
    def f_queryFunc(self, sql, params=None):
        """
        function:  执行查询语句函数
              in:  sql: SQL查询语句；
                   params: 参数（可选）；
             out:  None
          return:  查询结果列表（失败返回None）
          others:  Execute Query Operation Func
        """
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            return self.cursor.fetchall()
        except sqlite3.Error as e:
            return None

    # 批量执行操作函数
    def f_executeManyFunc(self, sql, data_list):
        """
        function:  批量执行操作函数
              in:  sql: SQL语句
                   data_list: 参数列表
             out:  None
          return:  受影响的总行数（失败返回-1）
          others:  Execute Batch Operations Func
        """
        try:
            self.cursor.executemany(sql, data_list)
            self.conn.commit()
            return self.cursor.rowcount
        except sqlite3.Error as e:
            self.conn.rollback()
            return -1

    # </editor-fold>

    # <editor-fold desc="事务管理">
    # 开始事务函数
    def f_beginTransactionFunc(self):
        """
        function:  开始事务函数
              in:  None
             out:  None
          return:  None
          others:  Begin Transaction Func
        """
        self.conn.execute("BEGIN")

    # 提交事务函数
    def f_commitTransactionFunc(self):
        """
        function:  提交事务函数
              in:  None
             out:  None
          return:  None
          others:  Commit Transaction Func
        """
        self.conn.commit()

    # 回滚事务函数
    def f_rollbackTransactionFunc(self):
        """
        function:  回滚事务函数
              in:  None
             out:  None
          return:  None
          others:  Rollback Transaction Func
        """
        self.conn.rollback()

    # </editor-fold>

    # <editor-fold desc="实用功能">
    # 检查表是否存在函数
    def f_tableExistsFunc(self, table_name):
        """
        function:  检查表是否存在函数
              in:  table_name: 表名称
             out:  None
          return:  bool 是否存在
          others:  Check Table Existence Func
        """
        check_sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?"
        return bool(self.f_queryFunc(check_sql, (table_name,)))

    # 获取表字段信息函数
    def f_getTableColumnsFunc(self, table_name):
        """
        function:  获取表字段信息函数
              in:  table_name: 表名称
             out:  None
          return:  字段信息列表
          others:  Get Table Columns Info Func
        """
        pragma_sql = f"PRAGMA table_info({table_name})"
        return self.f_queryFunc(pragma_sql)

    # 清空表数据函数
    def f_clearTableFunc(self, table_name):
        """
        function:  清空表数据函数
              in:  table_name: 表名称
             out:  None
          return:  受影响的行数
          others:  Clear Table Data Func
        """
        return self.f_executeFunc(f"DELETE FROM {table_name}")

    # </editor-fold>

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.f_closeFunc()


if __name__ == '__main__':
    # 初始化数据库连接
    db = ctSQLiteHelper("test.db")

    # 创建表
    create_table_sql = """CREATE TABLE IF NOT EXISTS users (
                        id INTEGER PRIMARY KEY,
                        name TEXT NOT NULL,
                        age INTEGER)"""
    db.f_executeFunc(create_table_sql)

    # 插入数据
    insert_sql = "INSERT INTO users (name, age) VALUES (?, ?)"
    print(db.f_executeFunc(insert_sql, ("Alice", 25)))  # 输出影响行数

    # 批量插入
    users = [("Bob", 30), ("Charlie", 35), ("David", 40)]
    print(db.f_executeManyFunc(insert_sql, users))  # 输出影响总行数

    # 查询数据
    query_sql = "SELECT * FROM users WHERE age > ?"
    print(db.f_queryFunc(query_sql, (28,)))

    # 事务操作示例
    try:
        db.f_beginTransactionFunc()
        db.f_executeFunc("UPDATE users SET age = ? WHERE name = ?", (26, "Alice"))
        db.f_executeFunc("DELETE FROM users WHERE name = ?", ("David",))
        db.f_commitTransactionFunc()
    except:
        db.f_rollbackTransactionFunc()

    # 关闭连接
    db.f_closeFunc()
